using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Reflection;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Configuration;
using MySql.Data.MySqlClient;

namespace Loader
{
    public partial class DatabaseCreation : Form
    {
        public static string db_USER = ConfigurationManager.AppSettings["BILL_USER"];
        public static string db_PASSWORD = ConfigurationManager.AppSettings["BILL_PASSWORD"];
        public static string user = null;
        public static string pass = null;
        public static string DBServer = null;
        private string SQL_CONN = null;
        private FormMain formMain = null;

        public DatabaseCreation(FormMain _formMain)
        {
            InitializeComponent();
            formMain = _formMain;
        }

        private void button1_Click(object sender, EventArgs evArg)
        {
            user = txtRootId.Text;
            pass = txtRootPass.Text;

            MySql.Data.MySqlClient.MySqlConnection.ClearAllPools();
            DialogResult reply = System.Windows.Forms.MessageBox.Show(this, "Warning! This command will drop any existing database named '" + DBName.Text + "'.", "Loader Warning", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning);
            if (reply == DialogResult.OK)
            {
                try
                {
                    button1.Enabled = false;
                    button2.Enabled = false;
                    reply = System.Windows.Forms.MessageBox.Show("The new database will be given a new user login: '" + ConfigurationManager.AppSettings["BILL_USER"] + "' and password: '" + ConfigurationManager.AppSettings["BILL_PASSWORD"] + "'. This database user/password can be configured in this application's configuration file. Do you want to continue?", "Database Creation Information", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                    if (reply == DialogResult.No)
                    {
                        button1.Enabled = true;
                        button2.Enabled = true;
                        this.Hide();
                    }
                    else
                    {
                        Wait.Show();
                        this.Refresh();
                        createDB();
                        Wait.Hide();
                        this.Refresh();
                        button1.Enabled = true;
                        button2.Enabled = true;
                        this.Hide();
                    }
                }
                catch (Exception e)
                {
                    System.Windows.Forms.MessageBox.Show("Processing Error! Exception is: " + e.Message, "Loader Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    this.Hide();
                    return;
                }
            }
            else
                return;
        }

        private void createDB()
        {
            string configDB = "";
            string dropDB = null;

            SQL_CONN = "server=" + FormMain.TRADE_DB_HOST + ";Database=INFORMATION_SCHEMA;uid=" + user + ";pwd=" + pass;
            MySqlConnection sqlConnection = new MySqlConnection(SQL_CONN);

            try
            {
                sqlConnection.Open();
                dropDB = "DROP DATABASE IF EXISTS " + DBName.Text;
                MySqlCommand sqlCommand = new MySqlCommand(dropDB, sqlConnection);
                sqlCommand.CommandTimeout = 1200;
                sqlCommand.CommandType = CommandType.Text;

                //Drop DB
                sqlCommand.ExecuteNonQuery();

                //Create DB
                string strCreateDB = "CREATE DATABASE " + DBName.Text;
                sqlCommand.CommandText = strCreateDB;
                sqlCommand.ExecuteNonQuery();

                db_USER = formMain.UserName.Text;
                db_PASSWORD = formMain.Password.Text;

                string checkuser = "SELECT count(*) FROM MYSQL.USER WHERE USER = '" + db_USER + "'";
                string createlogin = "CREATE USER '" + db_USER + "'@'%' IDENTIFIED BY '" + db_PASSWORD + "'";

                string grantprivilege = "GRANT ALL ON " + DBName.Text + ".* TO " + db_USER + "@'%' IDENTIFIED BY '" + db_PASSWORD + "'; FLUSH PRIVILEGES;";


                //check exist
                sqlCommand.CommandText = checkuser;
                int numberofUser = Convert.ToInt32(sqlCommand.ExecuteScalar());

                
                if (numberofUser.Equals(0))
                {
                    //create login
                    sqlCommand.CommandText = createlogin;
                    sqlCommand.ExecuteNonQuery();
                }

                //grant privilege
                sqlCommand.CommandText = grantprivilege;
                sqlCommand.ExecuteNonQuery();

                //Logout of Master
                sqlConnection.Close();
                System.Data.SqlClient.SqlConnection.ClearAllPools();


                //Login as newly created user.  
                //Don't need to but rather test connectivity here right away to make sure DB in good state for new trade user.
                SQL_CONN = "server=" + FormMain.TRADE_DB_HOST + ";Database=" + DBName.Text + ";uid=" + db_USER + ";pwd=" + db_PASSWORD;
                sqlConnection = new MySqlConnection(SQL_CONN);
                sqlConnection.Open();
                string createCommand = "USE " + DBName.Text + ";";
                Assembly assembly = Assembly.GetExecutingAssembly();


                //Create Schema for DB

                using (Stream dataStream = assembly.GetManifestResourceStream("DBLoader.createdb.sql"))
                {
                    System.IO.StreamReader file = new System.IO.StreamReader(dataStream);
                    createCommand = file.ReadToEnd();
                    file.Close();
                }

                sqlCommand = new MySqlCommand(createCommand, sqlConnection);
                sqlCommand.CommandType = CommandType.Text;
                sqlCommand.ExecuteNonQuery();
                sqlConnection.Close();

                formMain.dbName.Text = DBName.Text;
                formMain.UserName.Text = db_USER;
                formMain.Password.Text = db_PASSWORD;

                MessageBox.Show("Successfully created database " + DBName.Text + ". A new database login/user with name: '" + db_USER + "' and password: '" + db_PASSWORD + "' was added to the new database. The new database is ready to load!", "Database Creation Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
            catch (Exception e)
            {
                if (sqlConnection.State == ConnectionState.Open)
                    sqlConnection.Close();
                System.Data.SqlClient.SqlConnection.ClearAllPools();
                sqlConnection.Dispose();
                System.Windows.Forms.MessageBox.Show(this, "Processing Error! Exception is: " + e.Message, "Loader Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            this.Close();
        }
    }
}